package hh.util.jdbc;

import hh.util.Asserts;
import hh.util.CommonUtil;
import hh.util.Fun;
import hh.util.Log;
import hh.util.bean.BeanUtil;
import hh.util.coll.CollUtil;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.Setter;
import lombok.Value;
import lombok.experimental.Accessors;

import java.beans.PropertyDescriptor;
import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * @author HuangHuai on 2019-06-24 09:33
 */

@SuppressWarnings("unchecked")
public class JdbcUtil {

    public static final char identifierQuote = '`';
    public static final char valueQuote = '\'';

    static Log log = Log.instance;
    static String defaultUrl = "jdbc:mysql://localhost:3306";


    public static final Map<String, Class<?>> typeMapping = (Map<String, Class<?>>)
            Collections.unmodifiableMap(CollUtil.newHashMapKvs(
                    "tinyint", Integer.class,
                    "int", Integer.class,
                    "bigint", Long.class,
                    "datetime", Date.class,
                    "timestamp", Date.class,
                    "varchar", String.class,
                    "text", String.class,
                    "json", String.class,
                    "decimal", BigDecimal.class
            ));

    public static final Map<String, Class<?>> typeAlias = (Map<String, Class<?>>)
            Collections.unmodifiableMap(CollUtil.newHashMapKvs(
                    "short", Short.class,
                    "integer", Integer.class,
                    "int", Integer.class,
                    "float", Float.class,
                    "double", Double.class,
                    "string", String.class,

                    "date", Date.class,
                    "decimal", BigDecimal.class

            ));

    public static BatchInsert batchInsert(Connection conn, String sql) {
        return new BatchInsert(conn, sql);
    }

    public static void main(String[] args) {
        Connection connection = null;
        Select select = new Select();


    }


    @Accessors(fluent = true)
    @Data
    public static class CRUD {
        private Connection connection;

    }


    @Accessors(chain = true)
    @Data
    public static class Select extends CRUD {

    }


    @Accessors(chain = true)
    @Data
    public static class BatchInsert {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
        int count;
        String sql;
        Connection connection;
        PreparedStatement prep;
        StringBuilder sb;
        int batchSize = 1000;
        int lengthLimit; //bytes limit
        boolean prepStatement;

        public BatchInsert(Connection connection, String sql) {
            if (!sql.contains("values")) {
                sql = sql + " values";
            }
            if (sql.contains("?")) {
                prepStatement = true;
                try {
                    prep = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            this.sql = sql;
            this.connection = connection;
            sb = new StringBuilder(10000);
            sb.append(sql);
        }

        public void setFormat(SimpleDateFormat format) {
            this.format = format;
        }

        public void setCount(int count) {
            this.count = count;
        }

        public void setBatchSize(int batchSize) {
            this.batchSize = batchSize;
        }

        public void complete() {
            try {
                if (prepStatement) {
                    prep.executeBatch();
                    prep.close();
                } else if (sb.length() > sql.length()) {
                    PreparedStatement p = connection.prepareStatement(sb.toString());
                    p.executeUpdate();
                    p.close();
                }
            } catch (Exception e) {
                throw new RuntimeException(e);
            }

        }

        public void setArgs(Object... args) {
            try {
                count++;
                if (prepStatement) {
                    JdbcUtil.setSqlArgs(prep, args);
                    prep.addBatch();
                    if (count % batchSize == 0) {
                        count = 0;
                        prep.executeBatch();
                        prep.clearBatch();
                        // prep.cl
                    }
                } else {
                    sb.append(JdbcUtil.values(count == 1, args));
                    if (count % batchSize == 0) {
                        count = 0;
                        PreparedStatement p = connection.prepareStatement(sb.toString());
                        p.executeUpdate();
                        sb.setLength(sql.length());
                        p.close();
                    }

                }

            } catch (Exception e) {
                throw new RuntimeException("sql=" + sb.toString(), e);
            }
        }
    }


    public static Connection getConnection(String url, String user, String password) {
        if (url == null) {
            url = defaultUrl;
        }
        //        if (!url.contains("connectTimeout")) { //&connectTimeout=60000
        //            if (url.contains("&")) {
        //                url += "&connectTimeout=6000&autoReconnect=true";
        //            }else {
        //                url += "?connectTimeout=6000&autoReconnect=true";
        //            }
        //        }
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            throw new RuntimeException("获取数据库连接失败,url=" + url, e);
        }
    }

    public static long count(Connection conn, String sql) {
        return (long) selectOneRowOneColumn(conn, sql);
    }

    /**
     * 查询 only一行一列，如果有多行多列报错
     */
    public static Object selectOneRowOneColumn(Connection conn, String sql, Object... sqlArgs) {
        Fun.F11Throw<ResultSet, Object> consumer = rs -> {
            if (rs.next()) {
                Asserts.True(rs.getMetaData().getColumnCount() > 1, "希望获得一列, 但是查询到多列. sql={}", sql);
                Object object = rs.getObject(1);
                Asserts.True(rs.next(), "希望获得一行, 但是查询到多行. sql={}", sql);
                return object;
            }
            return null;
        };
        return doQuery(conn, sql, null, consumer, null, sqlArgs);
    }


    /**
     * 查询 only一行 如果有多行报错
     * queryOneRowMap的map代表返回一个map
     */
    public static Map<String, Object> selectOneRowMap(Connection conn, String sql, Object... sqlArgs) {
        Fun.F11Throw<ResultSet, Map<String, Object>> consumer = rs -> {
            if (rs.next()) {
                ResultSetMetaData metaData = rs.getMetaData();
                Map<String, Object> val = rsToMap(metaData, metaData.getColumnCount(), rs);
                Asserts.True(rs.next(), "希望获得一行, 但是查询到多行. sql={}", sql);
                return val;
            }
            return new HashMap<>(0);
        };
        return doQuery(conn, sql, null, consumer, null, sqlArgs);
    }

    /**
     * 查询 only一行 如果有多行报错
     * queryOneRowBean的Bean代表返回一个Bean
     * type不允许为null
     */
    public static <R> R selectOneRowBean(Connection conn, String sql, Class<R> type, Object... sqlArgs) {
        Fun.F11Throw<ResultSet, R> consumer = rs -> {
            if (rs.next()) {
                ResultSetMetaData metaData = rs.getMetaData();
                R o = rsToBean(metaData, metaData.getColumnCount(), rs, type.newInstance(), BeanUtil.getPropertyDesc(type));
                Asserts.True(rs.next(), "希望获得一行, 但是查询到多行. sql={}", sql);
                return o;
            }
            return null;
        };
        return doQuery(conn, sql, null, consumer, null, sqlArgs);
    }

    /**
     * listMap 代表有多条数据，每条数据用一个map封装
     */
    public static List<Map<String, Object>> listMap(Connection conn, String sql, Object... sqlArgs) {
        return doQuery(conn, sql, null, JdbcUtil::rsToListMap, null);
    }

    /**
     * listBean 代表有多条数据，每条数据用一个 Bean 封装
     * type不允许为null
     */
    public static <R> List<R> listBean(Connection conn, String sql, Class<R> type, Object... sqlArgs) {
        return doQuery(conn, sql, type, r -> rsToListBean(r, type), null);
    }

    /**
     * listMapStream 代表有多条数据，每条数据用一个 Map 封装，但是不返回，一条条的供消费者消费
     */
    public static void listMapStream(Connection conn, String sql, Fun.F10Throw<Map<String, Object>> consumer, Object... sqlArgs) {
        doQuery(conn, sql, null, null, consumer, sqlArgs);
    }

    /**
     * listBeanStream 代表有多条数据，每条数据用一个 bean 封装，但是不返回，一条条的供消费者消费
     */
    public static <R> void listBeanStream(Connection conn, String sql, Class<R> type, Fun.F10Throw<R> consumer, Object... sqlArgs) {
        doQuery(conn, sql, type, null, consumer, sqlArgs);
    }

    /**
     * 处理结果集，如果不是stream的，返回List<Bean> 或者List<Map>
     * 如果是Stream的,返回null
     *
     * @param consumerType   List<Bean>的 bean类型
     * @param streamConsumer 如果不为空,流式处理
     * @param consumer       如果不为空 返回List<xxx> 如果是bean，其实用不到这里的beantype，因为这里的consumer（rsToBean）可以传递进来
     * @param <C>            Bean type 用于stream消费，stream consumer没法传进来type所以这里必须传递
     * @param <R>            return type
     */
    private static <C, R> R doQuery(Connection conn,
                                    String sql,
                                    Class<C> consumerType,
                                    Fun.F11Throw<ResultSet, R> consumer,
                                    Fun.F10Throw<C> streamConsumer,
                                    Object... sqlArgs) {
        PreparedStatement prep = null;
        try {
            prep = getPreparedStatement(conn, sql, streamConsumer != null, sqlArgs);
            ResultSet rs = prep.executeQuery();
            if (streamConsumer == null) {
                // 正常消费，正常返回
                return consumer.apply(rs);
            } else {
                // stream, 不返回
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                if (consumerType != null) {
                    Map<String, PropertyDescriptor> propertyDesc = BeanUtil.getPropertyDesc(consumerType);
                    while (rs.next()) {
                        streamConsumer.apply(rsToBean(metaData, columnCount, rs, consumerType.newInstance(), propertyDesc));
                    }
                } else {
                    while (rs.next())
                        streamConsumer.apply((C) rsToMap(metaData, columnCount, rs));
                }
                return null;
            }
        } catch (SQLException e) {
            throw new RuntimeException(CommonUtil.format("执行sql出错,Msg：{}. sql={}", e.getMessage(), sql), e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            CommonUtil.closeQuietly(false, prep);
        }

    }

    public static int update(Connection conn, String sql, Object... sqlArgs) {
        PreparedStatement prep = null;
        try {
            prep = getPreparedStatement(conn, sql, false, sqlArgs);
            return prep.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(CommonUtil.format("执行sql出错,Msg：{}. sql={}", e.getMessage(), sql), e);
        } finally {
            CommonUtil.closeQuietly(false, prep);
        }
    }

    public static PreparedStatement getPreparedStatement(Connection conn, String sql, boolean stream, Object... sqlArgs) throws SQLException {
        PreparedStatement prep;
        if (stream) {
            prep = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            prep.setFetchSize(Integer.MIN_VALUE);
        } else
            prep = conn.prepareStatement(sql);
        return setSqlArgs(prep, sqlArgs);
    }

    public static PreparedStatement setSqlArgs(PreparedStatement prep, Object... sqlArgs) throws SQLException {
        if (sqlArgs != null && sqlArgs.length != 0) {
            for (int i = 0; i < sqlArgs.length; i++) {
                if (sqlArgs[i] == null) {
                    prep.setNull(i + 1, Types.VARCHAR);
                } else {
                    prep.setObject(i + 1, sqlArgs[i]);
                }
            }
        }
        return prep;
    }

    public static List<Map<String, Object>> rsToListMap(ResultSet rs) {
        List<Map<String, Object>> list = new ArrayList<>(256);
        try {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (rs.next()) {
                list.add(rsToMap(metaData, columnCount, rs));
            }
        } catch (Exception e) {
            throw new RuntimeException("从ResultSet获取数据失败：" + e.getMessage(), e);
        }
        return list;
    }

    private static Map<String, Object> rsToMap(ResultSetMetaData metaData, int columnCount, ResultSet rs) throws SQLException {
        HashMap<String, Object> map = new HashMap<>(columnCount * 2);
        for (int j = 1; j <= columnCount; j++) {
            map.put(metaData.getColumnLabel(j), rs.getObject(j));
        }
        return map;
    }

    public static <T> List<T> rsToListBean(ResultSet rs, Class<T> type) {
        List<T> data = new ArrayList<>(256);
        try {
            Map<String, PropertyDescriptor> nameToPropDesc = BeanUtil.getPropertyDesc(type);
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (rs.next()) {
                data.add(rsToBean(metaData, columnCount, rs, type.newInstance(), nameToPropDesc));
            }
        } catch (Exception e) {
            throw new RuntimeException("从ResultSet获取数据失败：" + e.getMessage(), e);
        }
        return data;
    }

    private static <T> T rsToBean(ResultSetMetaData metaData, int columnCount, ResultSet rs, T instance,
                                  Map<String, PropertyDescriptor> nameToPropDesc) throws Exception {
        for (int j = 1; j <= columnCount; j++) {
            PropertyDescriptor propertyDescriptor = nameToPropDesc.get(metaData.getColumnLabel(j));
            if (propertyDescriptor != null) {
                propertyDescriptor.getWriteMethod().invoke(instance, rs.getObject(j));
            }
        }
        return instance;
    }

    public static String quoteIdentifier(String identifier) {
        if (identifier.charAt(0) == identifierQuote) {
            return identifier;
        } else {
            return identifierQuote + identifier + identifierQuote;
        }
    }

    public static Object quoteValue(Object value) {
        if (value instanceof CharSequence) {
            if (((CharSequence) value).charAt(0) == '\'') {
                return value;
            } else {
                return "'" + value + "'";
            }
        } else {
            return value;
        }
    }

    public static StringBuilder values(boolean first, Object... arr) {
        if (arr == null || arr.length == 0) return new StringBuilder();
        StringBuilder sb = new StringBuilder(200);
        if (!first) {
            sb.append(',');
        }
        sb.append('(');
        for (int i = 0; i < arr.length; i++) {
            Object o = arr[i];
            if (i != 0) {
                sb.append(',');
            }
            if (o instanceof String) {
                sb.append(valueQuote).append(o).append(valueQuote);
            } else {
                sb.append(o);
            }
        }
        sb.append(')');
        return sb;
    }


}
